package com.bonc.rms.utils.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bonc.rms.utils.JDBCUtil;
import com.bonc.rms.utils.JDBCUtil.QueryResult;

public class EmployeeRRUtilImpl {

	/**
	 * 
	 * @param year
	 * @param departmentId
	 * @return 这个年份的现役员工
	 */
	public static List<String> getActiveDutyYear(String year,
			Integer departmentStandardId) {

		String sql = null;
		QueryResult qr = null;
		List<String> employeeList = new ArrayList<>();

		List<Integer> departmentIdlist = new ArrayList<>();
		departmentIdlist = WastageInfoUtilImpl
				.getDepartmentId(departmentStandardId);
		if (departmentIdlist == null || departmentIdlist.size() == 0) {

			sql = "SELECT job_number FROM talent WHERE YEAR(leave_date)=? OR YEAR(leave_date)>? OR YEAR(entry_date)=? OR YEAR(entry_date)<? OR ISNULL(leave_date)";
			qr = JDBCUtil.query(sql, year, year, year, year);// year
			ResultSet rs = qr.getResultSet();

			try {
				while (rs.next()) {
					employeeList.add(rs.getString(1));

				}

			} catch (SQLException e) {

				e.printStackTrace();
			} finally {
				qr.close();

			}
		} else {

			StringBuffer departmentId = new StringBuffer();
			departmentId = WastageInfoUtilImpl.stringUtil(departmentIdlist);
			sql = "SELECT job_number FROM talent WHERE YEAR(leave_date)=? OR YEAR(leave_date)>? OR YEAR(entry_date)=? OR YEAR(entry_date)<? OR ISNULL(leave_date) AND department_id IN " + departmentId;
			
			qr = JDBCUtil.query(sql, year, year, year, year);// year
			ResultSet rs = qr.getResultSet();

			try {
				while (rs.next()) {
					employeeList.add(rs.getString(1));

				}

			} catch (SQLException e) {

				e.printStackTrace();
			} finally {
				qr.close();

			}

		}

		return employeeList;

	}

	/**
	 * 
	 * @param employeeList
	 * @return 这个年份的每个现役员工总工资的计算信息 入职时间 试用期转正时间 离职时间 调薪日期(list) 距离本年度最近的调薪日期
	 */
	public static Map<String, Map<String, Object>> getEmployeeInfo(
			List<String> employeeList, String year) {
		String sql = null;
		QueryResult qr = null;
		QueryResult qr2 = null;
		QueryResult qr3 = null;

		Map<String, Map<String, Object>> allEmployeeInfoMap = new HashMap<>();
		for (String jobNumber : employeeList) {
			Map<String, Object> employeeInfoMap = new HashMap<>();

			sql = "SELECT leave_date, entry_date, trial_period_end_date FROM talent WHERE job_number=?";
			qr = JDBCUtil.query(sql, jobNumber);
			ResultSet rs = qr.getResultSet();

			sql = "SELECT adjust_date FROM salary_record WHERE  job_number=? AND YEAR(adjust_date)=? ORDER BY adjust_date  DESC";
			qr2 = JDBCUtil.query(sql, jobNumber, year);
			ResultSet rs2 = qr2.getResultSet();

			sql = "SELECT adjust_date FROM salary_record WHERE  job_number=? AND YEAR(adjust_date)<? ORDER BY adjust_date  DESC LIMIT 0,1";
			qr3 = JDBCUtil.query(sql, jobNumber, year);
			ResultSet rs3 = qr3.getResultSet();

			employeeInfoMap.put("员工工号", jobNumber);

			try {
				while (rs.next()) {

					employeeInfoMap.put("离职时间", rs.getString(1));
					employeeInfoMap.put("入职时间", rs.getString(2));
					employeeInfoMap.put("试用期转正时间", rs.getString(3));
				}
			} catch (SQLException e) {

				e.printStackTrace();
			} finally {
				qr.close();

			}
			try {

				List<String> adjustDateList = new ArrayList<>();
				while (rs2.next()) {
					adjustDateList.add(rs2.getString(1));
					employeeInfoMap.put("调薪日期", adjustDateList);
				}

			} catch (SQLException e) {

				e.printStackTrace();
			} finally {
				qr2.close();
			}
			try {
				while (rs3.next()) {

					employeeInfoMap.put("距离本年度最近的调薪日期", rs3.getString(1));

				}

			} catch (SQLException e) {

				e.printStackTrace();
			} finally {
				qr3.close();

			}
			allEmployeeInfoMap.put(jobNumber, employeeInfoMap);
		}
		return allEmployeeInfoMap;

	}

	/**
	 * 获取总工资方法
	 */
	public static String getTotalWages(Map<String, Map<String, Object>> map,
			String year) {

		int sumSalary = 0;

		for (Map<String, Object> employeeMap : map.values()) {

			int everyoneSumSalary = 0;// 个人年度总工资

			String jobNumberStr = (String) employeeMap.get("员工工号");

			String leaveStr = (String) employeeMap.get("离职时间");// 本年度离职的信息

			String leaveYearStr;
			String leaveMonthStr;
			int leaveMonthInt = 0;
			if (leaveStr != null) {
				leaveYearStr = leaveStr.substring(0, 4);
				leaveMonthStr = (leaveStr.substring(5, 6)).equals("0") ? leaveStr
						.substring(6, 7) : leaveStr.substring(5, 7);
				leaveMonthInt = Integer.parseInt(leaveMonthStr);
			}

			String entryStr = (String) employeeMap.get("入职时间");
			String entryYearStr = entryStr.substring(0, 4);
			String entryMonthStr = (entryStr.substring(5, 6)).equals("0") ? entryStr
					.substring(6, 7) : entryStr.substring(5, 7);
			int entryMonthInt = Integer.parseInt(entryMonthStr);

			// String trialStr = (String) employeeMap.get("试用期转正时间");
			// String trialYear = trialStr.substring(0, 4);
			// String trialMonth = (trialStr.substring(5, 6)).equals("0") ?
			// trialStr
			// .substring(6, 7) : trialStr.substring(5, 7);

			String finallyAdjust = (String) employeeMap.get("距离本年度最近的调薪日期");
			String finallyAdjustYear;
			String finallyAdjustMonth;
			if (finallyAdjust != null) {
				finallyAdjustYear = finallyAdjust.substring(0, 4);
				finallyAdjustMonth = (finallyAdjust.substring(5, 6))
						.equals("0") ? finallyAdjust.substring(6, 7)
						: finallyAdjust.substring(5, 7);
			}

			List<String> adjustList = new ArrayList<>();
			adjustList = (List<String>) employeeMap.get("调薪日期");
			// 本年度有离职
			if (year.equals(leaveStr)) {

				// ***************************本年度有离职且无调薪
				if (adjustList == null || adjustList.size() == 0) {
					// 本年度之前不存在过调薪
					if (finallyAdjust == null || finallyAdjust.equals("")) {
						int trialSalary = 0;
						trialSalary = getTrialMonthSalary(jobNumberStr);// 试用期每个月总工资

						if (year.equals(entryYearStr)) {// 本年度入得职

							everyoneSumSalary = (leaveMonthInt - entryMonthInt)
									* trialSalary;
						} else {// 本年度之前入得职

							everyoneSumSalary = (leaveMonthInt - 1)
									* trialSalary;
						}
					} else {
						// 本年度之前存在过调薪
						int adjustSalary = 0;
						adjustSalary = getAdjustMonthSalary(jobNumberStr,
								finallyAdjust);// 调薪后每个月总工资
						everyoneSumSalary = (leaveMonthInt - 1) * adjustSalary;
					}

				} else {
					// ***************************本年度有离职且有调薪
					for (String adjustStr : adjustList) {// adjustList中日期从12月份——>1月份顺序排列
						int i = 0;
						int lastAdjustMonthInt = 0;

						String adjustYear = adjustStr.substring(0, 4);
						String adjustMonth = (adjustStr.substring(5, 6))
								.equals("0") ? adjustStr.substring(6, 7)
								: adjustStr.substring(5, 7);
						int adjustMonthInt = Integer.parseInt(adjustMonth);

						// 还没转正就以离职
						// 调薪日期大于离职日期只有一种可能就是实习生离职
						if (adjustMonthInt > leaveMonthInt) {
							int trialSalary = 0;
							trialSalary = getTrialMonthSalary(jobNumberStr);

							if (year.equals(entryYearStr)) {// 本年度入得职

								everyoneSumSalary = (leaveMonthInt - entryMonthInt)
										* trialSalary;
							} else {// 本年度之前入得职

								everyoneSumSalary = (leaveMonthInt - 1)
										* trialSalary;
							}
						} else {// 正常调薪
							int adjustSalary = 0;
							adjustSalary = getAdjustMonthSalary(jobNumberStr,
									finallyAdjust);
							if (i == 0) {// 离职到第一次调薪总工资
								everyoneSumSalary = (leaveMonthInt - adjustMonthInt)
										* adjustSalary;

							} else {// 后面调薪之间总工资
								everyoneSumSalary += (lastAdjustMonthInt - adjustMonthInt)
										* adjustSalary;
							}
							if (i == adjustList.size()) {// 本年第一次调薪之前的总工资

								if (finallyAdjust == null
										|| finallyAdjust.equals("")) {// 本年度之前不存在过调薪

									int trialSalary = 0;
									trialSalary = getTrialMonthSalary(jobNumberStr);

									if (year.equals(entryYearStr)) {// 本年度入得职

										everyoneSumSalary += (lastAdjustMonthInt - entryMonthInt)
												* trialSalary;
									} else {// 本年度之前入得职

										everyoneSumSalary += (lastAdjustMonthInt - 1)
												* trialSalary;
									}
								} else {
									// 本年度之前存在过调薪
									int finallyAdjustSalary = 0;
									finallyAdjustSalary = getAdjustMonthSalary(
											jobNumberStr, finallyAdjust);
									everyoneSumSalary += (lastAdjustMonthInt - 1)
											* finallyAdjustSalary;
								}

							}
						}
						i += 1;
						lastAdjustMonthInt = adjustMonthInt;
					}

				}
			} else {

				// ***************************本年度无离职且无调薪
				if (adjustList == null || adjustList.size() == 0) {
					// 本年度之前不存在过调薪
					if (finallyAdjust == null || finallyAdjust.equals("")) {
						int trialSalary = 0;
						trialSalary = getTrialMonthSalary(jobNumberStr);

						if (year.equals(entryYearStr)) {// 本年度入得职

							everyoneSumSalary = (12 - entryMonthInt)
									* trialSalary;
						} else {// 本年度之前入得职

							everyoneSumSalary = 12 * trialSalary;
						}
					} else {
						// 本年度之前存在过调薪
						int adjustSalary = 0;
						adjustSalary = getAdjustMonthSalary(jobNumberStr,
								finallyAdjust);
						everyoneSumSalary = 12 * adjustSalary;
					}

				} else {
					// ***************************本年度无离职但有调薪
					for (String adjustStr : adjustList) {// adjustList中日期从12月份——>1月份顺序排列
						int i = 0;
						int lastAdjustMonthInt = 0;

						String adjustYear = adjustStr.substring(0, 4);
						String adjustMonth = (adjustStr.substring(5, 6))
								.equals("0") ? adjustStr.substring(6, 7)
								: adjustStr.substring(5, 7);
						int adjustMonthInt = Integer.parseInt(adjustMonth);

						// 正常调薪
						int adjustSalary = 0;
						adjustSalary = getAdjustMonthSalary(jobNumberStr,
								finallyAdjust);
						if (i == 0) {// 离职到第一次调薪总工资
							everyoneSumSalary = (12 - adjustMonthInt)
									* adjustSalary;

						} else {// 后面调薪之间总工资
							everyoneSumSalary += (lastAdjustMonthInt - adjustMonthInt)
									* adjustSalary;
						}
						if (i == adjustList.size()) {// 本年第一次调薪之前的总工资
							if (finallyAdjust == null
									|| finallyAdjust.equals("")) {
								int trialSalary = 0;
								trialSalary = getTrialMonthSalary(jobNumberStr);

								if (year.equals(entryYearStr)) {// 本年度入得职

									everyoneSumSalary = (lastAdjustMonthInt - entryMonthInt)
											* trialSalary;
								} else {// 本年度之前入得职

									everyoneSumSalary = (lastAdjustMonthInt - 1)
											* trialSalary;
								}

							} else {
								// 本年度之前存在过调薪
								int finallyAdjustSalary = 0;
								finallyAdjustSalary = getAdjustMonthSalary(
										jobNumberStr, finallyAdjust);
								everyoneSumSalary += (lastAdjustMonthInt - 1)
										* finallyAdjustSalary;
							}

						}

						i += 1;
						lastAdjustMonthInt = adjustMonthInt;
					}
				}

			}
			sumSalary += everyoneSumSalary;
		}

		return Integer.toString(sumSalary);

	}

	/**
	 * 根据员工号，调薪日期,获取这个月度调薪后的工资，绩效，奖金 总和
	 */
	public static int getAdjustMonthSalary(String jobNumberStr, String adjustStr) {
		String sql = null;
		QueryResult qr = null;
		// ''
		sql = "SELECT adjusted_basic_wage , adjusted_performance , adjusted_bonus , adjusted_t_level FROM salary_record WHERE job_number=? AND adjust_date=?";
		qr = JDBCUtil.query(sql, jobNumberStr, adjustStr);
		ResultSet rs = qr.getResultSet();
		int basic = 0;
		int performance = 0;
		int bonus = 0;
		int levelT = 0;
		int sum = 0;
		try {
			while (rs.next()) {
				basic = rs.getInt(1);
				performance = rs.getInt(2);
				bonus = rs.getInt(3);
				levelT = rs.getInt(4);
			}
			sum = basic + performance + bonus + levelT;
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			qr.close();
		}
		return sum;

	}

	/**
	 * 根据员工,获取实习期每个月工资，绩效，奖金 总和
	 */
	public static int getTrialMonthSalary(String jobNumberStr) {
		String sql = null;
		QueryResult qr = null;
		sql = "SELECT trial_basic_wage , trial_performance , trial_bonus , trial_t_level FROM salary WHERE job_number= ?";
		qr = JDBCUtil.query(sql, jobNumberStr);
		ResultSet rs = qr.getResultSet();

		int basic = 0;
		int performance = 0;
		int bonus = 0;
		int levelT = 0;
		int sum = 0;

		try {
			while (rs.next()) {
				basic = rs.getInt(1);
				performance = rs.getInt(2);
				bonus = rs.getInt(3);
				levelT = rs.getInt(4);
			}
			sum = basic + performance + bonus + levelT;

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			qr.close();
		}
		return sum;

	}

	/**
	 * 
	 * @param salaryList
	 * @return 通过每年总工资求增长率
	 */
	public static List<String> getSalaryGrowRrate(List<String> salaryList) {

		float rate = 0;
		List<String> rateList = new ArrayList<>();
		rateList.add("0");

		DecimalFormat decimalFormat = new DecimalFormat(".00");
		float lastSumSalary = 0;
		float i = 0;
		for (String string : salaryList) {
			float sumSalary = Integer.parseInt(string);

			if (i == 0) {
				i++;
				lastSumSalary = sumSalary;
				continue;
			} else {
				if (lastSumSalary == 0) {
					rateList.add("0");
					lastSumSalary = sumSalary;
					continue;
				} else {
					rate = (sumSalary / lastSumSalary - 1) * 100;

					String rateStr = decimalFormat.format(rate);
					rateList.add(rateStr);
					i++;
					lastSumSalary = sumSalary;

				}

			}

		}

		return rateList;
	}

}
